/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hbPackage;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Enumeration;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author windySwing
 */
public class DatabaseAccess {

    private String connectionString;
    private String user;
    private String password;
    private Connection con;

    public DatabaseAccess() {
        this.connectionString = "jdbc:sqlserver://localhost:1433;database=dbHealthyBeauty";
        this.user = "sa";
        this.password = "";
    }

    public String ReplaceEscapeChars(String str) {
        if (str == null) {
            return str;
        }

        if (str == "") {
            return str;
        }

        str = str.replace("'", "''");

        return str;
    }

//    public ResultSet Select(String query) {
//        PreparedStatement ps;
//        ResultSet rs = null;
//        try {
//            //ResultSet rs;
//
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            con = DriverManager.getConnection(connectionString, user, password);
//
//            ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//            rs = ps.executeQuery();
//
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return rs;
//    }

    public boolean Execute(String query) {
        boolean result = false;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps;

            ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            int res = ps.executeUpdate();

            if (res > 0) {
                result = true;
            }

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean Insert(String table, ArrayList data) {
        boolean result = false;
        try {
           // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
          //  con = DriverManager.getConnection(connectionString, user, password);

            String strQuery = "";
            strQuery += "INSERT INTO " + table + " VALUES(";

            for (Object item : data) {
                strQuery += "'" + ReplaceEscapeChars(item.toString()) + "',";
            }
            strQuery = strQuery.substring(0, strQuery.length() - 1);
            strQuery += ")";

            result = Execute(strQuery);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean Update(String table, String[] key, ArrayList data, String[] condition) {
        String strQuery = "";
        strQuery += "UPDATE " + table + " SET ";


        for (int i = 0; i < data.size(); i++) {
            strQuery += key[i] + "='" + ReplaceEscapeChars(data.get(i).toString()) + "',";
        }
        strQuery = strQuery.substring(0, strQuery.length() - 1);

        for (int i = 0; i < condition.length; i++) {
            if (i == 0) {
                strQuery += " WHERE " + condition[i];
            } else {
                strQuery += " AND " + condition[i];
            }
        }

        boolean result = Execute(strQuery);
        return result;

    }

    public boolean Delete(String table, String[] condition) {
        String strQuery = "";
        strQuery += "DELETE " + table + " ";

        for (int i = 0; i < condition.length; i++) {
            if (i == 0) {
                strQuery += " WHERE " + condition[i];
            } else {
                strQuery += " AND " + condition[i];
            }
        }


        boolean result = Execute(strQuery);
        return result;
    }

    public ArrayList getListItem() {
        ArrayList arr = new ArrayList();

        String query = "select * from Item";
        
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                itemClass _item = new itemClass();
                _item.setId(rs.getInt("itemID"));
                _item.setCode(rs.getString("code"));
                _item.setItemName(rs.getString("itemName"));
                _item.setDescription(rs.getString("description"));
                _item.setUsage(rs.getString("usage"));
                _item.setImage(rs.getString("image"));
                _item.setGroup_ID(rs.getInt("group_ID"));
                _item.setPrice(rs.getFloat("price"));

                arr.add(_item);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arr;
    }

    public itemClass getItem(int _id) {
        itemClass _item = new itemClass();

        //String query = "select * from Item where itemID=" + _id;
        String query = "select * from Item where itemID=?";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _id);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                _item.setId(rs.getInt("itemID"));
                _item.setCode(rs.getString("code"));
                _item.setItemName(rs.getString("itemName"));
                _item.setDescription(rs.getString("description"));
                _item.setUsage(rs.getString("usage"));
                _item.setImage(rs.getString("image"));
                _item.setGroup_ID(rs.getInt("group_ID"));
                _item.setPrice(rs.getFloat("price"));
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return _item;
    }

    public ArrayList getWareHouseList(int _storeID){
        ArrayList arr = new ArrayList();
        SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yyyy");
        String query = "select * from WareHouse where store_ID=?";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _storeID);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                wareHouseClass _wareHouse = new wareHouseClass();
                _wareHouse.setItem_ID(rs.getInt("item_ID"));
                _wareHouse.setStore_ID(_storeID);
                _wareHouse.setQuantity(rs.getInt("quantity"));
                _wareHouse.setReceiveDate(sf.format(rs.getDate("receiveDate")));
                

                arr.add(_wareHouse);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arr;
    }

    public ArrayList<discountClass> getListDiscount (int _storeID){
        ArrayList<discountClass> arr = new ArrayList<discountClass>();

        SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yyyy");
        String query = "select * from Discount where store_ID=? order by status";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _storeID);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                discountClass _discount = new discountClass();
                _discount.setDiscountID(rs.getInt("discountID"));
                _discount.setItem_ID(rs.getInt("item_ID"));
                _discount.setStore_ID(_storeID);
                _discount.setPercent(rs.getInt("percentage"));
                _discount.setFromDate(rs.getDate("fromDate"));
                _discount.setToDate(rs.getDate("toDate"));
                _discount.setType(rs.getString("type"));
                _discount.setStatus(rs.getString("status"));

                arr.add(_discount);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return arr;
    }

    public wareHouseClass getWareHouse(int _storeID, int _itemID) {
        wareHouseClass _wareHouse = new wareHouseClass();

        //String query = "select * from WareHouse where store_ID=" + _storeID + " and item_ID=" + _itemID;
        String query = "select * from WareHouse where store_ID=? and item_ID=?";
        SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yyyy");

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _storeID);
            ps.setInt(2, _itemID);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                _wareHouse.setItem_ID(_itemID);
                _wareHouse.setStore_ID(_storeID);
                _wareHouse.setQuantity(rs.getInt("quantity"));
                _wareHouse.setReceiveDate(sf.format(rs.getDate("receiveDate")));
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return _wareHouse;
    }

    public int getLastOrderID() {
        int _lastID = 0;

        String query = "select orderID from [Order] order by orderID";        

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                _lastID = rs.getInt("orderID");
            }

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return _lastID;
    }

    public ArrayList getListOrderDetail(int _orderID) {
        ArrayList arr = new ArrayList();

        //String query = "select * from OrderDetail where order_id=" + _orderID;
        String query = "select * from OrderDetail where order_id=?";
        
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _orderID);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                orderDetailClass _orderDetail = new orderDetailClass();
                _orderDetail.setOrder_ID(_orderID);
                _orderDetail.setItem_ID(rs.getInt("item_ID"));
                _orderDetail.setItemName(rs.getString("itemName"));
                _orderDetail.setQuantity(rs.getInt("quantity"));
                _orderDetail.setPrice(rs.getDouble("price"));
                _orderDetail.setDiscount(rs.getInt("discount"));
                _orderDetail.setTotalPrice(rs.getDouble("totalPrice"));
                _orderDetail.setFinalPrice(rs.getDouble("finalPrice"));

                arr.add(_orderDetail);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return arr;
    }

    public int getItemDiscount(int _itemID, int _storeID, String _status) {
        int _discountPercent = 0;

        ArrayList arr = new ArrayList();
        //String query = "select * from Discount where item_ID=" + _itemID + " and status='" + _status + "'";
        String query = "select * from Discount where item_ID=? and status=?";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _itemID);
            ps.setString(2, _status);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                discountClass _discount = new discountClass();
                _discount.setDiscountID(rs.getInt("discountID"));
                _discount.setStore_ID(rs.getInt("store_ID"));
                _discount.setItem_ID(_itemID);
                _discount.setPercent(rs.getInt("percentage"));
                _discount.setFromDate(rs.getDate("fromDate"));
                _discount.setToDate(rs.getDate("toDate"));
                _discount.setType(rs.getString("type"));
                _discount.setStatus(rs.getString("status"));

                arr.add(_discount);
            }

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        for (int i = 0; i < arr.size(); i++) {
            discountClass _discount = (discountClass) arr.get(i);
            if (_discount.getType().equalsIgnoreCase("national")) {
                _discountPercent = _discount.getPercent();
            } else if (_discount.getType().equalsIgnoreCase("local")) {
                if (_discount.getStore_ID() == _storeID) {
                    _discountPercent = _discount.getPercent();
                }
            }
        }

        return _discountPercent;
    }

    public orderClass getOrder(int _orderID) {
        orderClass _order = new orderClass();
        //String query = "select * from [Order] where orderID=" + _orderID;
        String query = "select * from [Order] where orderID=?";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionString, user, password);

            PreparedStatement ps = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, _orderID);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                _order.setOrder_ID(_orderID);
                _order.setStore_ID(rs.getInt("store_ID"));
                _order.setTotalPayment(rs.getDouble("totalPayment"));
                _order.setStatus(rs.getString("status"));
                _order.setDate(rs.getDate("date"));
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return _order;
    }
}
